﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataObjects
{
    public static class Db
    {
        public static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=QUANLYTHUVIEN;Integrated Security=True";
        public static SqlConnection sqlConnection = new SqlConnection(connectionString);
        public static SqlCommand sqlCommand;
        public static SqlDataAdapter dataAdapter;        

        private static void Initialize(string sqlString,object[] parms)
        {            
            sqlCommand = new SqlCommand(sqlString, sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                foreach (object index in parms)
                {
                    sqlCommand.Parameters.Add(index);
                }
            }
        }

        public static DataTable Read(string sqlString)
        {            
            DataTable table = new DataTable();
            
            Initialize(sqlString, null);
            dataAdapter = new SqlDataAdapter(sqlCommand);

            sqlConnection.Open();
            dataAdapter.Fill(table);
            sqlConnection.Close();

            return table;
        }

        public static DataTable Read(string sqlString, object[] parms)
        {
            DataTable table = new DataTable();

            Initialize(sqlString, parms);
            dataAdapter = new SqlDataAdapter(sqlCommand);

            sqlConnection.Open();
            dataAdapter.Fill(table);
            sqlConnection.Close();

            return table;
        }

        public static string ExecuteStoreProcedure(string sqlString, object[] parms)
        {
            Initialize(sqlString, parms);
            
            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            } catch (SqlException e)
            {
                return e.Message;
            }
            finally
            {
                sqlConnection.Close();
            }
            return String.Format("Success");
        }       
        
    }
}
